﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION LEVEL2FUNCTIONLIST
(
	-- Add the parameters for the function here
	@EntityID int,
	@Relationship nvarchar(50)=NULL
)
RETURNS 
TABLE
AS
RETURN (
SELECT * FROM
(
SELECT        dbo.ViewLevel1Relationships.Entity1 As EntityID, dbo.ViewLevel1Relationships.ShortRelationshipName AS Level1ShortRelName, dbo.ViewLevel1Relationships.RelName AS Level1Relationship, 
                         dbo.ViewLevel1Relationships.Entity2, ViewLevel1Relationships_1.ShortRelationshipName AS Level2ShortRelName, ViewLevel1Relationships_1.RelName AS Level2Relationship, 
                         ViewLevel1Relationships_1.Entity2 AS Entity3, dbo.ViewLevel1Relationships.FORWARD AS Level1Forward, ViewLevel1Relationships_1.FORWARD AS Level2Forward, 
                         dbo.ViewLevel1Relationships.Level2Rights & ViewLevel1Relationships_1.Level1Rights AS Entity1CanAccessEntity3, dbo.DefinedFunctions.FunctionName, dbo.DefinedFunctions.FunctionCommand, 
                         dbo.DefinedFunctions.ViewName, dbo.DefinedFunctions.EditAttributes, dbo.DefinedFunctions.DisplayAttributeName
FROM            dbo.DefinedFunctions INNER JOIN
                         dbo.DefinedFunctionRelationships ON dbo.DefinedFunctions.FunctionID = dbo.DefinedFunctionRelationships.FunctionID INNER JOIN
                         dbo.ViewLevel1Relationships INNER JOIN
                         dbo.ViewLevel1Relationships AS ViewLevel1Relationships_1 ON dbo.ViewLevel1Relationships.Entity2 = ViewLevel1Relationships_1.Entity1 AND 
                         dbo.ViewLevel1Relationships.Entity1 <> ViewLevel1Relationships_1.Entity2 ON dbo.DefinedFunctionRelationships.RelationshipShortName = ViewLevel1Relationships_1.ShortRelationshipName AND 
                         dbo.DefinedFunctionRelationships.Forward = ViewLevel1Relationships_1.FORWARD
WHERE        (dbo.ViewLevel1Relationships.Level2Rights & ViewLevel1Relationships_1.Level1Rights = 1)
) AS T
WHERE
				T.EntityID = @EntityID AND (T.Level1ShortRelName = @Relationship OR @Relationship IS NULL)
)